package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import br.com.bean.DisciplinaTurmaBean;
import br.com.bean.NotaFaltaBimestreBean;
import br.com.bean.TurmaBean;

public class NotaFaltaBimestreDao {

	public void inserir(Connection conn, NotaFaltaBimestreBean value) throws SQLException {
		String sql = "INSERT INTO nota_falta_bimestre( id_matricula, " +
				"id_bimestre, id_diciplina, aulasdadas, nota, falta, id_aluno) VALUES ( ?, ?, ?, ?, ?, ?)";

		PreparedStatement ps = conn.prepareStatement(sql);

		for(int i=0; i< ps.getMaxFieldSize();i++){

			ps.setInt(1, value.getNmatricula().getId());
			ps.setInt(2, value.getBimestre().getId());
			ps.setInt(3, value.getDisciplina().getId());
			ps.setInt(4, value.getAulasdadas());
			ps.setDouble(5, value.getNota());
			ps.setInt(6, value.getFalta());
			ps.setInt(7, value.getAluno().getId());
			ps.execute();
			ps.close();

		}
	}

	public void excluir(Connection conn, NotaFaltaBimestreBean value) throws Exception {

		String sql = "DELETE FROM nota_falta_bimestre WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.execute();
	}

	public void alterar(Connection conn, NotaFaltaBimestreBean value) throws Exception {

		String sql = "UPDATE nota_falta_bimestre SET id_matricula=?, " +
				"id_bimestre=?, id_disciplina=?, aulasdadas=?, nota=?, falta=?, id_aluno WHERE  id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getNmatricula().getId());
		ps.setInt(2, value.getBimestre().getId());
		ps.setInt(3, value.getDisciplina().getId());
		ps.setInt(4, value.getAulasdadas());
		ps.setDouble(5, value.getNota());
		ps.setInt(6, value.getFalta());
		ps.setInt(7, value.getAluno().getId());
		ps.execute();
		ps.close();
	}

	public ArrayList<NotaFaltaBimestreBean> getLista(Connection conn, NotaFaltaBimestreBean value) throws Exception{

		String sql = " SELECT * FROM nota_falta_bimestre where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		ArrayList<NotaFaltaBimestreBean> al = new ArrayList<NotaFaltaBimestreBean>();
		while(rs.next()){
			NotaFaltaBimestreBean a = new NotaFaltaBimestreBean();
			a.getNmatricula().setId(rs.getInt("id_matricula"));
			a.getBimestre().setId(rs.getInt("bimestre"));
			a.setAulasdadas(rs.getInt("aulasdadas"));
			a.getDisciplina().setId(rs.getInt("disciplina"));
			a.setNota(rs.getDouble("nota"));
			a.setFalta(rs.getInt("falta"));
			a.getAluno().setId(rs.getInt("id_aluno"));
			al.add(a);
		}
		return al;
	}

	public ArrayList<NotaFaltaBimestreBean> getLista(Connection conn) throws Exception{


		String sql = " SELECT * FROM nota_falta_bimestre";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ArrayList<NotaFaltaBimestreBean> al = new ArrayList<NotaFaltaBimestreBean>();
		while(rs.next()){
			NotaFaltaBimestreBean a = new NotaFaltaBimestreBean();
			a.getNmatricula().setId(rs.getInt("id_matricula"));
			a.getBimestre().setId(rs.getInt("bimestre"));
			a.setAulasdadas(rs.getInt("aulasdadas"));
			a.getDisciplina().setId(rs.getInt("disciplina"));
			a.setNota(rs.getDouble("nota"));
			a.setFalta(rs.getInt("falta"));
			a.getAluno().setId(rs.getInt("id_aluno"));
			al.add(a);
		}
		return al;
	}

	// canhoto para buscar os alunos da turma no banco
	public List<NotaFaltaBimestreBean> getCanhoto (Connection conn, TurmaBean value) throws Exception{

		String sql = "select mat.id matricula, mat.nchamada matrnchamad, al.id idaluno, al.nome alunonome" +
				" FROM cad_matricula mat, cad_aluno al, cad_turma ct " +
				"where al.id=mat.id_aluno and ct.id=mat.id_turma and ct.id=? order by mat.nchamada";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();

		ArrayList<NotaFaltaBimestreBean> nt = new ArrayList<NotaFaltaBimestreBean>();
		while(rs.next()){
			NotaFaltaBimestreBean a = new NotaFaltaBimestreBean();
			a.getNmatricula().setId(rs.getInt("matricula"));
			a.getNmatricula().setNchamada(rs.getInt("matrnchamad"));
			a.getAluno().setId(rs.getInt("idaluno"));
			a.getAluno().setNome(rs.getString("alunonome").toString());
			nt.add(a);

		}
		return nt;		
	}

	public List<NotaFaltaBimestreBean> getCanhoto (Connection conn, DisciplinaTurmaBean value) throws Exception{

		StringBuilder sb = new StringBuilder();
		sb.append("select mat.id      matricula,");
		sb.append("mat.nchamada     matrnchamad,"); 
		sb.append("al.id                idaluno,"); 
		sb.append("al.nome            alunonome ");
		sb.append("FROM cad_matricula       mat,"); 
		sb.append("cad_aluno                 al,"); 
		sb.append("cad_turma                 ct,"); 
		sb.append("disciplina_turma          dt ");
		sb.append("where    al.id=mat.id_aluno  ");
		sb.append("and      ct.id=mat.id_turma  ");
		sb.append("and      ct.id=dt.id_turma   ");
		sb.append("and      dt.id=?             ");
		sb.append("order by mat.nchamada        ");

		PreparedStatement ps = conn.prepareStatement(sb.toString());
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();

		ArrayList<NotaFaltaBimestreBean> nt = new ArrayList<NotaFaltaBimestreBean>();
		while(rs.next()){
			NotaFaltaBimestreBean a = new NotaFaltaBimestreBean();
			a.getNmatricula().setId(rs.getInt("matricula"));
			a.getNmatricula().setNchamada(rs.getInt("matrnchamad"));
			a.getAluno().setId(rs.getInt("idaluno"));
			a.getAluno().setNome(rs.getString("alunonome").toString());
			nt.add(a);

		}
		return nt;		
	}
}